﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TianShiProject.EntityFrameworkCore;
using TianShiProject.Models.Common;
using TianShiProject.Models.Materials.MaterialStatistics;
using Volo.Abp.EntityFrameworkCore;

namespace TianShiProject.Materials.MaterialStatistics
{
    public class MaterialStatisticsRepository : DapperDbContextRepository, IMaterialStatisticsRepository
    {
        public MaterialStatisticsRepository(IDbContextProvider<TianShiProjectDbContext> dbContextProvider) : base(dbContextProvider)
        {
        }

        #region 物资统计——查询分页显示
        /// <summary>
        /// 物资统计——查询分页显示
        /// </summary>
        /// <param name="GoodsName"></param>
        /// <param name="Texture"></param>
        /// <param name="Nationw"></param>
        /// <param name="Province"></param>
        /// <param name="BeginTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public async Task<PageResponse<MaterialStatisticsDto>> GetMaterialStatisticsPageShow(string GoodsName,int Texture, int Nationw,int Province, DateTime? BeginTime, DateTime? EndTime, int pageindex = 1, int pagesize = 3)
        {
            PageResponse<MaterialStatisticsDto> response = new PageResponse<MaterialStatisticsDto>();
            var sql = "select a.GoodsName,a.Typey,a.Texture,a.Specification,a.Nationw,a.Province,b.Id,b.StatisticsNumber,b.BeginTime,b.EndTime,b.BuyerAmount,b.ReceiveAmount,b.SurplusAmount,b.IsDelete,c.CountryName as CName,d.CountryName as DName,e.DictionaryName as EName,f.DictionaryName as FName from PurchaseRequest a join MaterialStatistics b on a.Id=b.Id join Country c on a.Nationw=c.Id join Country d on a.Province=d.Id join Dictionary e on a.Texture=e.Id join Dictionary f on a.Typey=f.Id where b.IsDelete=0";
            if (!string.IsNullOrWhiteSpace(GoodsName))
            {
                sql += $" and a.GoodsName like '%" + GoodsName + "%'";
            }
            if (Texture > 0)
            {
                sql += $" and a.Texture='{Texture}'";
            }
            if (Nationw > 0)
            {
                sql += $" and a.Nationw='{Nationw}'";
            }
            if (Province > 0)
            {
                sql += $" and a.Province='{Province}'";
            }
            if (BeginTime != null)
            {
                sql += $" and b.BeginTime='{BeginTime}'";
            }
            if (EndTime != null)
            {
                sql += $" and b.EndTime='{EndTime}'";
            }
            var list = await this.QueryAsync<MaterialStatisticsDto>(sql, null);
            response.PageTotal = list.Count;
            sql += $" order by b.Id offset {(pageindex - 1) * pagesize} rows fetch next {pagesize} rows only";
            var lists = await this.QueryAsync<MaterialStatisticsDto>(sql, null);
            response.PageData = lists;
            return response;
        }
        #endregion
    }
}
